﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Data Access Object for Reminder
/// </summary>
public class ReminderDA
{
	public ReminderDA()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public int Delete(Reminder reminder)
    {
        string command = "sp_tb_user_DeleteUserReminder";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        SqlCommand sqlCmd = new SqlCommand(command, sqlCon);
        sqlCon.Open();
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCmd.Parameters.Add(new SqlParameter("@reminderID", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@result", SqlDbType.Int, 32, ""));
        sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;

        sqlCmd.Parameters["@reminderID"].Value = reminder.ID;

        sqlCmd.ExecuteNonQuery();

        int result = 0;

        try
        {
            result = Convert.ToInt16(sqlCmd.Parameters["@result"].Value);
        }
        catch (Exception ex)
        {
            result = 0;
        }

        sqlCon.Close();

        return result;
    }

    public int Update(Reminder reminder)
    {
        string command = "sp_tb_user_UpdateUserReminder";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        SqlCommand sqlCmd = new SqlCommand(command, sqlCon);
        sqlCon.Open();
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCmd.Parameters.Add(new SqlParameter("@reminderID", SqlDbType.Int));
        sqlCmd.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@description", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@alarmTime", SqlDbType.SmallDateTime));
        sqlCmd.Parameters.Add(new SqlParameter("@result", SqlDbType.Int, 32, ""));
        sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;

        sqlCmd.Parameters["@reminderID"].Value = reminder.ID;
        sqlCmd.Parameters["@title"].Value = reminder.Title;
        sqlCmd.Parameters["@description"].Value = reminder.Description;
        sqlCmd.Parameters["@alarmTime"].Value = reminder.AlarmTime; //"12/21/2012 2:11:00 PM";

        sqlCmd.ExecuteNonQuery();

        int result = 0;

        try
        {
            result = Convert.ToInt16(sqlCmd.Parameters["@result"].Value);
        }
        catch (Exception ex)
        {
            result = 0;
        }

        sqlCon.Close();

        return result;
    }

    public int Insert(Reminder reminder)
    {
        string command = "sp_tb_user_InsertUserReminder";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        SqlCommand sqlCmd = new SqlCommand(command, sqlCon);
        sqlCon.Open();
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@description", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@alarmTime", SqlDbType.SmallDateTime));
        sqlCmd.Parameters.Add(new SqlParameter("@result", SqlDbType.Int, 32, ""));
        sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;

        sqlCmd.Parameters["@userID"].Value = reminder.Username;
        sqlCmd.Parameters["@title"].Value = reminder.Title;
        sqlCmd.Parameters["@description"].Value = reminder.Description;
        sqlCmd.Parameters["@alarmTime"].Value = reminder.AlarmTime;// "12/21/2012 2:11:00 PM";

        sqlCmd.ExecuteNonQuery();

        int result = 0;

        try
        {
            result = Convert.ToInt16(sqlCmd.Parameters["@result"].Value);
        }
        catch (Exception ex)
        {
            result = 0;
        }

        sqlCon.Close();

        return result;
    }

    public int Stop(Reminder reminder)
    {
        string command = "sp_tb_user_StopUserReminder";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        SqlCommand sqlCmd = new SqlCommand(command, sqlCon);
        sqlCon.Open();
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCmd.Parameters.Add(new SqlParameter("@reminderID", SqlDbType.NVarChar, 255, ""));
        sqlCmd.Parameters.Add(new SqlParameter("@result", SqlDbType.Int, 32, ""));
        sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;

        sqlCmd.Parameters["@reminderID"].Value = reminder.ID;

        sqlCmd.ExecuteNonQuery();

        int result = 0;

        try
        {
            result = Convert.ToInt16(sqlCmd.Parameters["@result"].Value);
        }
        catch (Exception ex)
        {
            result = 0;
        }

        sqlCon.Close();

        return result;
    }

    public int Postpone(Reminder reminder)
    {
        string command = "sp_tb_user_PostponeUserReminder";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        SqlCommand sqlCmd = new SqlCommand(command, sqlCon);
        sqlCon.Open();
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCmd.Parameters.Add(new SqlParameter("@reminderID", SqlDbType.Int));
        sqlCmd.Parameters.Add(new SqlParameter("@alarmTime", SqlDbType.SmallDateTime));
        sqlCmd.Parameters.Add(new SqlParameter("@result", SqlDbType.Int, 32, ""));
        sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;

        sqlCmd.Parameters["@reminderID"].Value = reminder.ID;
        sqlCmd.Parameters["@alarmTime"].Value = reminder.AlarmTime; //"12/21/2012 2:11:00 PM";

        sqlCmd.ExecuteNonQuery();

        int result = 0;

        try
        {
            result = Convert.ToInt16(sqlCmd.Parameters["@result"].Value);
        }
        catch (Exception ex)
        {
            result = 0;
        }

        sqlCon.Close();

        return result;
    }

    public DataTable SelectAllByUsername(Reminder reminder)
    {
        string command = "sp_tb_user_GetUserReminders @userID='" + reminder.Username + "'";
        SqlConnection sqlCon = new SqlConnection(DatabaseFunctions.GetConnectionString());
        sqlCon.Open();
        SqlDataAdapter sda = new SqlDataAdapter(command, sqlCon);

        DataTable dsReminders = new DataTable();
        sda.Fill(dsReminders);

        sqlCon.Close();
        return dsReminders;
    }        
}
